#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
-- 需求1：每天/每月/每年线上线下以及新老学员的意向用户个数
-- 插入语句
INSERT INTO hive.edu_online_ads.ads_time_line_member_num
SELECT
    year_code,
    month_code,
    day_code,
    online_offline,
    new_old_member,
    relation_num
FROM hive.edu_online_dws.dws_relationship
WHERE time_type = 'date' OR time_type = 'month' OR time_type = 'year' AND group_type = new_old_member;


-- 需求2：每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
INSERT INTO hive.edu_online_ads.ads_area_tlm_num
SELECT
    year_code,
    month_code,
    day_code,
    area,
    online_offline,
    new_old_member,
    relation_num
FROM hive.edu_online_dws.dws_relationship
WHERE time_type = 'date' OR time_type = 'month' OR time_type = 'year' AND group_type = 'area';


-- 需求3：每天/每月/每年各学科线上线下以及新老学员的意向用户个数Top10
INSERT INTO hive.edu_online_ads.ads_subject_tlm_num
SELECT
    year_code,
    month_code,
    day_code,
    subject_name,
    online_offline,
    new_old_member,
    relation_num
FROM hive.edu_online_dws.dws_relationship
WHERE time_type = 'date' OR time_type = 'month' OR time_type = 'year' AND group_type = 'subject'
order by relation_num desc limit 10;


-- 需求4：每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10
INSERT INTO hive.edu_online_ads.ads_school_tlm_num
SELECT
    year_code,
    month_code,
    day_code,
    school_name,
    online_offline,
    new_old_member,
    relation_num
FROM hive.edu_online_dws.dws_relationship
WHERE time_type = 'date' OR time_type = 'month' OR time_type = 'year' AND group_type = 'school'
order by relation_num desc limit 10;


-- 需求5：每天/每月/每年各来源渠道线上线下以及新老学员的意向用户个数
INSERT INTO hive.edu_online_ads.ads_channel_tlm_num
SELECT
    year_code,
    month_code,
    day_code,
    origin_channel,
    online_offline,
    new_old_member,
    relation_num
FROM hive.edu_online_dws.dws_relationship
WHERE time_type = 'date' OR time_type = 'month' OR time_type = 'year' AND group_type = 'origin_channel';


-- 需求6：每天/每月/每年==各咨询中心==线上线下以及新老学员的意向用户个数
INSERT INTO hive.edu_online_ads.ads_reference_tlm_num
SELECT
    year_code,
    month_code,
    day_code,
    reference_center_name,
    online_offline,
    new_old_member,
    relation_num
FROM hive.edu_online_dws.dws_relationship
WHERE time_type = 'date' OR time_type = 'month' OR time_type = 'year' AND group_type = 'reference_center';


-- 需求7：每天线上线下及新老学员的有效线索个数
INSERT INTO hive.edu_online_ads.ads_day_line_member_count
SELECT
    day_code,
    online_offline,
    new_old_member,
    valid_clue_num
FROM hive.edu_online_dws.dws_clue
WHERE time_type = 'date' AND group_type = 'new_old_member';


-- 需求8：每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数
INSERT INTO hive.edu_online_ads.ads_hour_line_member_rate
SELECT
    day_code,
    hour_code,
    online_offline,
    new_old_member,
    cast(((valid_clue_num/clue_num) * 100) as decimal(5,2)) AS valid_clue_rate

FROM hive.edu_online_dws.dws_clue
WHERE time_type = 'hour' AND group_type = 'new_old_member';
"
